﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InsuranceTests.classes;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace InsuranceTests.classes
{
    class dao
    {
        static private string Сon = @"Data Source=LOCALHOST\SQLEXPRESS;Initial Catalog=Test;
Integrated Security=True;Pooling=False";
        public static List<Contract> conList;
        public static List<User> userList;
        public static List<Flat> flatList;
        private Contract x;

        static public int Connection(string c)
        {
            try
            {
                SqlConnection connection = new SqlConnection(c);
                connection.Open();
                return 1;
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при установлении соединения: " + ex.Message);

                return 2;
            }
        }
//user's methods
        static public int WatchContracts()
        {
            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
                conList = new List<Contract>();
                SqlCommand сommand2 = new SqlCommand("select * from Contract", connection);
                SqlDataReader reader = сommand2.ExecuteReader();

                while (reader.Read())
                {
                    conList.Add(new Contract(Convert.ToInt32(reader["Id"]), Convert.ToInt32(reader["IdFlat"]), Convert.ToInt32(reader["IdAgent"]), Convert.ToBoolean(reader["Fire"]), Convert.ToBoolean(reader["Water"]), Convert.ToBoolean(reader["Wear"]), Convert.ToBoolean(reader["Problem"]), Convert.ToBoolean(reader["Loot"]), Convert.ToDateTime(reader["Date"]), Convert.ToInt32(reader["Sum"])));
                }

                reader.Close();
                if (conList != null)
                {
                    return 1;
                }
                else
                {
                    return 2;
                }

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при выборке данных: " + ex.Message);
                return 2;

            }
            connection.Close();
        }
        static public int WatchFlat(int num)
        {
            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
                flatList = new List<Flat>();
                SqlCommand сommand2 = new SqlCommand("select * from Flat where Id='" + num + "'", connection);
                SqlDataReader reader = сommand2.ExecuteReader();

                while (reader.Read())
                {
                    flatList.Add(new Flat(Convert.ToInt32(reader["Id"]), Convert.ToInt32(reader["IdClient"]), Convert.ToString(reader["Adress"]), Convert.ToBoolean(reader["LostFloor"]), Convert.ToInt32(reader["Rooms"]), Convert.ToDouble(reader["Square"]), Convert.ToInt32(reader["Year"]), Convert.ToInt32(reader["Price"])));
                }

                reader.Close();
                if (flatList != null)
                {
                    return 1;
                }
                else
                {
                    return 2;
                }

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при выборке данных: " + ex.Message);
                return 2;

            }
            connection.Close();
        }
        static public int SearchContract(int num)
        {
            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
                conList = new List<Contract>();
                SqlCommand сommand2 = new SqlCommand("select * from Contract where id='" + num + "'", connection);
                SqlDataReader reader = сommand2.ExecuteReader();
                while (reader.Read())
                {
                    conList.Add(new Contract(Convert.ToInt32(reader["Id"]), Convert.ToInt32(reader["IdFlat"]), Convert.ToInt32(reader["IdAgent"]), Convert.ToBoolean(reader["Fire"]), Convert.ToBoolean(reader["Water"]), Convert.ToBoolean(reader["Wear"]), Convert.ToBoolean(reader["Problem"]), Convert.ToBoolean(reader["Loot"]), Convert.ToDateTime(reader["Date"]), Convert.ToInt32(reader["Sum"])));
                }
                reader.Close();
                if (conList != null)
                {
                    return 1;
                }
                else
                {
                    return 2;
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при выборке данных: " + ex.Message);
                return 2;
            }
        }
//agent's methods
        static public int AddClient(Client rec)
        {

            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
                SqlCommand сommand1 = new SqlCommand("insert into Client (Certificate,PassportSerial,Birth,PassportNumber,Name,Surname,Phone,Email)" +
                    "VALUES ('" + rec.Certificate + "','" + rec.PassportSerial + "','" + rec.Birth + "','" + rec.PassportNumber + "','" + rec.Name + "','" + rec.Surname + "','" + rec.Phone + "','" + rec.Email+ "')", connection);
                сommand1.ExecuteNonQuery();
                return 1;

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при вставке данных: " + ex.Message);
                return 2;

            }
            connection.Close();

        }
        static public int AddFlat(Flat rec)
        {

            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
                SqlCommand сommand1 = new SqlCommand("insert into Flat (IdClient,Adress,LostFloor,Rooms,Square,Year,Price)" +
                    "VALUES ('" + rec.IdClient + "','" + rec.Adress + "','" + rec.LostFloor + "','" + rec.Rooms + "','" + rec.Square+ "','" + rec.Year + "','" + rec.Price + "')", connection);
                сommand1.ExecuteNonQuery();
                return 1;

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при вставке данных: " + ex.Message);
                return 2;

            }
            connection.Close();

        }
        static public int AddContract(Contract rec)
        {

            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
                SqlCommand сommand1 = new SqlCommand("insert into Contract (IdFlat,IdAgent,Fire,Water,Wear,Problem,Loot,Date,Sum)" +
                    "VALUES ('" + rec.IdFlat + "','" + rec.IdAgent + "','" + rec.Fire + "','" + rec.Water + "','" + rec.Wear + "','" + rec.Problem + "','" + rec.Loot + "','" + rec.Date + "','" + rec.Sum + "')", connection);
                сommand1.ExecuteNonQuery();
                return 1;

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при вставке данных: " + ex.Message);
                return 2;

            }
            connection.Close();

        }
        static public int Autorization(string p)
        {
            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
                userList = new List<User>();
                SqlCommand сommand2 = new SqlCommand("select * from User1 where Password='" + p + "'", connection);
                SqlDataReader reader = сommand2.ExecuteReader();

                while (reader.Read())
                {
                    userList.Add(new User(Convert.ToInt32(reader["Id"]),Convert.ToString(reader["Password"]), Convert.ToInt32(reader["Role"]), Convert.ToString(reader["Name"]), Convert.ToString(reader["Surname"]), Convert.ToInt32(reader["Phone"]), Convert.ToString(reader["Email"])));
                }

                reader.Close();
                if (userList != null)
                {
                    return 1;
                }
                else
                {
                    return 2;
                }

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при выборке данных: " + ex.Message);
                return 2;

            }
            connection.Close();
        }
        static public int UpdateClient(Client rec1, Client rec2)
        {
            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
                SqlCommand сommand1 = new SqlCommand("update Client set Certificate='" + rec2.Certificate +  "', PassportSerial='" +
            rec2.PassportSerial + "', PassportNumber='" +rec2.PassportNumber + "', Phone='"+ rec2.Phone + "', Email='" +
            rec2.Email + "', Surname='" +rec2.Surname + "'where Id='" + rec1.Id + "'", connection);
                сommand1.ExecuteNonQuery();
                return 1;

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при удалении данных: " + ex.Message);
                return 2;

            }
            connection.Close();
        }
//admin's methods
        static public int AddUser(User rec)
        {

            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();

            SqlCommand сommand1 = new SqlCommand("insert into User1 (Password,Role,Name,Surname,Phone,Email)" +
                "VALUES ('" + rec.Password + "','" + rec.Role + "','" + rec.Name + "','" + rec.Surname + "','" + rec.Phone + "','" + rec.Email + "')", connection);
            сommand1.ExecuteNonQuery();
            return 1;



            connection.Close();

        }
        static public int WatchUsers()
        {
            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
               userList = new List<User>();
                SqlCommand сommand2 = new SqlCommand("select * from User1", connection);
                SqlDataReader reader = сommand2.ExecuteReader();

                while (reader.Read())
                {
                    userList.Add(new User(Convert.ToInt32(reader["Id"]),Convert.ToString(reader["Password"]),Convert.ToInt32(reader["Role"]),Convert.ToString(reader["Name"]),Convert.ToString(reader["Surname"]),Convert.ToInt32(reader["Phone"]),Convert.ToString(reader["Email"])));
                }

                reader.Close();
                if (userList != null)
                {
                    return 1;
                }
                else
                {
                    return 2;
                }

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при выборке данных: " + ex.Message);
                return 2;

            }
            connection.Close();
        }
        static public int DeleteUser(User rec)
        {

            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
                SqlCommand сommand1 = new SqlCommand("delete from User1 where Id='" + rec.Id +"'", connection);
                сommand1.ExecuteNonQuery();
                return 1;

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при удалении данных: " + ex.Message);
                return 2;

            }
            connection.Close();
        }
        static public int UpdateUser(User rec1, User rec2)
        {

            SqlConnection connection = new SqlConnection(Сon);
            connection.Open();
            try
            {
                SqlCommand сommand1 = new SqlCommand("update User1 set Role='" + rec2.Role + "'where Id='" + rec1.Id + "'", connection);
                сommand1.ExecuteNonQuery();
                return 1;

            }
            catch (SqlException ex)
            {
                Console.WriteLine("Ошибка при удалении данных: " + ex.Message);
                return 2;

            }
            connection.Close();
        }
    }
}
